﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;
using System.Data.OracleClient;
using Xiaov.SmartWeb.Application.SupCan;

namespace Xiaov.SmartWeb.Application.HXZB
{
    public partial class SczbIndexData : System.Web.UI.Page
    {
        protected string nf = DateTime.Now.Year.ToString();
        protected string yf = (DateTime.Now.Month - 1).ToString();
        protected string dwbm = "HB"; 

        protected void Page_Load(object sender, EventArgs e)
        {
            nf = Request.QueryString["nf"];
            yf = Request.QueryString["yf"];
            dwbm = Request.QueryString["dwbm"];

            InitTable();
        }

        protected void InitTable()
        {           
            DataTable dt = new DataTable();
            dt.Columns.Add("zbmc");
            dt.Columns.Add("bysj");
            dt.Columns.Add("bnlj");
            dt.Columns.Add("qntqlj");
            dt.Columns.Add("zbbm");
            string sSql = "";
            #region 一、开井数
     /*       sSql = @"select a.yf,(a.kj-nvl(c.kj,0)) bysj,a.kj bnlj,b.kj qntqlj from 
                (select sum(kj) kj,yf from tz01 where nf={0} and yf={1} and zbbm in ('TZ010001','TZ010002','TZ010006') and dwbm='{2}' group by yf) a,
                (select sum(kj) kj,yf from tz01 where nf={0}-1 and yf={1} and zbbm in ('TZ010001','TZ010002','TZ010006') and dwbm='{2}' group by yf) b,
                (select sum(kj) kj,yf from tz01 where nf={0} and yf={1}-1 and zbbm in ('TZ010001','TZ010002','TZ010006') and dwbm='{2}' group by yf) c";
            OracleDataReader dr = (OracleDataReader)Maticsoft.DBUtility.DbHelperOra.ExecuteReader(string.Format(sSql, nf, yf, "HB"));
            if (dr.Read())
            {
                DataRow drow = dt.NewRow();
                drow[0] = "一、开井数";
                drow[1] = dr["bysj"].ToString();
                drow[2] = dr["bnlj"].ToString();
                drow[3] = dr["qntqlj"].ToString();
                drow[4] = "1";
                dt.Rows.Add(drow);
            }
            else
            {
                DataRow drow = dt.NewRow();
                drow[0] = "一、开井数";
                drow[1] = 0;
                drow[2] = 0;
                drow[3] = 0;
                drow[4] = "0";
                dt.Rows.Add(drow);
            }
            dr.Close();
            #endregion
            #region 油井
            sSql = @"select a.yf,(a.kj-nvl(c.kj,0)) bysj,a.kj bnlj,b.kj qntqlj,a.zbbm from 
                (select kj,yf,zbbm from tz01 where nf={0} and yf={1} and zbbm='TZ010002' and dwbm='{2}') a,
                (select kj,yf from tz01 where nf={0}-1 and yf={1} and zbbm='TZ010002' and dwbm='{2}') b,
                (select kj,yf from tz01 where nf={0} and yf={1}-1 and zbbm='TZ010002' and dwbm='{2}') c";
            dr = (OracleDataReader)Maticsoft.DBUtility.DbHelperOra.ExecuteReader(string.Format(sSql, nf, yf, "HB"));
            if (dr.Read())
            {
                DataRow drow = dt.NewRow();
                drow[0] = "  油井";
                drow[1] = dr["bysj"].ToString();
                drow[2] = dr["bnlj"].ToString();
                drow[3] = dr["qntqlj"].ToString();
                drow[4] = dr["zbbm"].ToString();
                dt.Rows.Add(drow);
            }
            else
            {
                DataRow drow = dt.NewRow();
                drow[0] = "  油井";
                drow[1] = 0;
                drow[2] = 0;
                drow[3] = 0;
                drow[4] = "0";
                dt.Rows.Add(drow);
            }
            dr.Close();
            #endregion
            #region 气井
            sSql = @"select a.yf,(a.kj-nvl(c.kj,0)) bysj,a.kj bnlj,b.kj qntqlj,a.zbbm from 
                (select kj,yf,zbbm from tz01 where nf={0} and yf={1} and zbbm='TZ010001' and dwbm='{2}') a,
                (select kj,yf from tz01 where nf={0}-1 and yf={1} and zbbm='TZ010001' and dwbm='{2}') b,
                (select kj,yf from tz01 where nf={0} and yf={1}-1 and zbbm='TZ010001' and dwbm='{2}') c";
            dr = (OracleDataReader)Maticsoft.DBUtility.DbHelperOra.ExecuteReader(string.Format(sSql, yf, nf, "HB"));
            if (dr.Read())
            {
                DataRow drow = dt.NewRow();
                drow[0] = "  气井";
                drow[1] = dr["bysj"].ToString();
                drow[2] = dr["bnlj"].ToString();
                drow[3] = dr["qntqlj"].ToString();
                drow[4] = dr["zbbm"].ToString();
                dt.Rows.Add(drow);
            }
            else
            {
                DataRow drow = dt.NewRow();
                drow[0] = "  气井";
                drow[1] = 0;
                drow[2] = 0;
                drow[3] = 0;
                drow[4] = "0";
                dt.Rows.Add(drow);
            }
            dr.Close();
            #endregion
            #region 水井
            sSql = @"select a.yf,(a.kj-nvl(c.kj,0)) bysj,a.kj bnlj,b.kj qntqlj,a.zbbm from 
                (select kj,yf,zbbm from tz01 where nf={0} and yf={1} and zbbm='TZ010006' and dwbm='{2}') a,
                (select kj,yf from tz01 where nf={0}-1 and yf={1} and zbbm='TZ010006' and dwbm='{2}') b,
                (select kj,yf from tz01 where nf={0} and yf={1}-1 and zbbm='TZ010006' and dwbm='{2}') c";
            dr = (OracleDataReader)Maticsoft.DBUtility.DbHelperOra.ExecuteReader(string.Format(sSql, yf, nf, "HB"));
            if (dr.Read())
            {
                DataRow drow = dt.NewRow();
                drow[0] = "  水井";
                drow[1] = dr["bysj"].ToString();
                drow[2] = dr["bnlj"].ToString();
                drow[3] = dr["qntqlj"].ToString();
                drow[4] = dr["zbbm"].ToString();
                dt.Rows.Add(drow);
            }
            else
            {
                DataRow drow = dt.NewRow();
                drow[0] = "  水井";
                drow[1] = 0;
                drow[2] = 0;
                drow[3] = 0;
                drow[4] = "0";
                dt.Rows.Add(drow);
            }
            dr.Close();*/
            #endregion
            DataRow drow1 = dt.NewRow();
            drow1[0] = "一、产量";
            drow1[1] = "";
            drow1[2] = "";
            drow1[3] = "";
            drow1[4] = "5";
            dt.Rows.Add(drow1);
            #region 原油
            sSql = @"select round((a.bnlj-nvl(b.bnlj,0)),0) bysj,round(a.bnlj,0) bnlj,round(a.qntqlj,0) qntqlj,a.zbbm from 
                (select bnlj,qntqlj,zbbm from tg03 t where nf={1} and yf={0} and zbbm='TG030008' and dwbm='{2}') a,
                (select bnlj from tg03 t where nf={1} and yf={0}-1 and zbbm='TG030008' and dwbm='{2}') b ";
            OracleDataReader dr = (OracleDataReader)Maticsoft.DBUtility.DbHelperOra.ExecuteReader(string.Format(sSql, yf, nf, "HB"));
            if (dr.Read())
            {
                DataRow drow = dt.NewRow();
                drow[0] = "  原油";
                drow[1] = dr["bysj"].ToString();
                drow[2] = dr["bnlj"].ToString();
                drow[3] = dr["qntqlj"].ToString();
                drow[4] = dr["zbbm"].ToString();
                dt.Rows.Add(drow);
            }
            else
            {
                DataRow drow = dt.NewRow();
                drow[0] = "  原油";
                drow[1] = "";
                drow[2] = "";
                drow[3] = "";
                drow[4] = "0";
                dt.Rows.Add(drow);
            }
            dr.Close();
            #endregion
            #region 天然气
            sSql = @"select round((a.bnlj-nvl(b.bnlj,0)),0) bysj,round(a.bnlj,0) bnlj,round(a.qntqlj,0) qntqlj,a.zbbm from 
                (select bnlj,qntqlj,zbbm from tg03 t where nf={1} and yf={0} and zbbm='TG030026' and dwbm='{2}') a,
                (select bnlj from tg03 t where nf={1} and yf={0}-1 and zbbm='TG030026' and dwbm='{2}') b ";
            dr = (OracleDataReader)Maticsoft.DBUtility.DbHelperOra.ExecuteReader(string.Format(sSql, yf, nf, "HB"));
            if (dr.Read())
            {
                DataRow drow = dt.NewRow();
                drow[0] = "  天然气";
                drow[1] = dr["bysj"].ToString();
                drow[2] = dr["bnlj"].ToString();
                drow[3] = dr["qntqlj"].ToString();
                drow[4] = dr["zbbm"].ToString();
                dt.Rows.Add(drow);
            }
            else
            {
                DataRow drow = dt.NewRow();
                drow[0] = "  天然气";
                drow[1] = "";
                drow[2] = "";
                drow[3] = "";
                drow[4] = "0";
                dt.Rows.Add(drow);
            }
            dr.Close();
            #endregion
            #region 凝析油
            sSql = @"select round((a.bnlj-nvl(b.bnlj,0)),0) bysj,round(a.bnlj,0) bnlj,round(a.qntqlj,0) qntqlj,a.zbbm from 
                (select bnlj,qntqlj,zbbm from tg03 t where nf={1} and yf={0} and zbbm='TG030012' and dwbm='{2}') a,
                (select bnlj from tg03 t where nf={1} and yf={0}-1 and zbbm='TG030012' and dwbm='{2}') b ";
            dr = (OracleDataReader)Maticsoft.DBUtility.DbHelperOra.ExecuteReader(string.Format(sSql, yf, nf, "HB"));
            if (dr.Read())
            {
                DataRow drow = dt.NewRow();
                drow[0] = "  凝析油";
                drow[1] = dr["bysj"].ToString();
                drow[2] = dr["bnlj"].ToString();
                drow[3] = dr["qntqlj"].ToString();
                drow[4] = dr["zbbm"].ToString();
                dt.Rows.Add(drow);
            }
            else
            {
                DataRow drow = dt.NewRow();
                drow[0] = "  凝析油";
                drow[1] = "";
                drow[2] = "";
                drow[3] = "";
                drow[4] = "0";
                dt.Rows.Add(drow);
            }
            dr.Close();
            #endregion
            DataRow drow2 = dt.NewRow();
            drow2[0] = "二、销售量";
            drow2[1] = "";
            drow2[2] = "";
            drow2[3] = "";
            drow2[4] = "9";
            dt.Rows.Add(drow2);
            #region 销售量原油
            sSql = @"select round((a.bnlj-nvl(b.bnlj,0)),0) bysj,round(a.bnlj,0) bnlj,round(a.qntqlj,0) qntqlj,a.zbbm from 
                (select bnlj,qntqlj,zbbm from tg03 t where nf={1} and yf={0} and zbbm='TG030015' and dwbm='{2}') a,
                (select bnlj from tg03 t where nf={1} and yf={0}-1 and zbbm='TG030015' and dwbm='{2}') b ";
            dr = (OracleDataReader)Maticsoft.DBUtility.DbHelperOra.ExecuteReader(string.Format(sSql, yf, nf, "HB"));
            if (dr.Read())
            {
                DataRow drow = dt.NewRow();
                drow[0] = "  原油";
                drow[1] = dr["bysj"].ToString();
                drow[2] = dr["bnlj"].ToString();
                drow[3] = dr["qntqlj"].ToString();
                drow[4] = dr["zbbm"].ToString();
                dt.Rows.Add(drow);
            }
            else
            {
                DataRow drow = dt.NewRow();
                drow[0] = "  原油";
                drow[1] = "";
                drow[2] = "";
                drow[3] = "";
                drow[4] = "0";
                dt.Rows.Add(drow);
            }
            dr.Close();
            #endregion
            #region 销售量天然气
            sSql = @"select round((a.bnlj-nvl(b.bnlj,0)),0) bysj,round(a.bnlj,0) bnlj,round(a.qntqlj,0) qntqlj,a.zbbm from 
                (select bnlj,qntqlj,zbbm from tg03 t where nf={1} and yf={0} and zbbm='TG030031' and dwbm='{2}') a,
                (select bnlj from tg03 t where nf={1} and yf={0}-1 and zbbm='TG030031' and dwbm='{2}') b ";
            dr = (OracleDataReader)Maticsoft.DBUtility.DbHelperOra.ExecuteReader(string.Format(sSql, yf, nf, "HB"));
            if (dr.Read())
            {
                DataRow drow = dt.NewRow();
                drow[0] = "  天然气";
                drow[1] = dr["bysj"].ToString();
                drow[2] = dr["bnlj"].ToString();
                drow[3] = dr["qntqlj"].ToString();
                drow[4] = dr["zbbm"].ToString();
                dt.Rows.Add(drow);
            }
            else
            {
                DataRow drow = dt.NewRow();
                drow[0] = "  天然气";
                drow[1] = "";
                drow[2] = "";
                drow[3] = "";
                drow[4] = "0";
                dt.Rows.Add(drow);
            }
            dr.Close();
            #endregion
            #region 原油配置量
            sSql = @"select round((a.bnlj-nvl(b.bnlj,0)),0) bysj,round(a.bnlj,0) bnlj,round(a.qntqlj,0) qntqlj,a.zbbm from 
                (select bnlj,qntqlj,zbbm from tg03 t where nf={1} and yf={0} and zbbm='TG030016' and dwbm='{2}') a,
                (select bnlj from tg03 t where nf={1} and yf={0}-1 and zbbm='TG030016' and dwbm='{2}') b ";
            dr = (OracleDataReader)Maticsoft.DBUtility.DbHelperOra.ExecuteReader(string.Format(sSql, yf, nf, "HB"));
            if (dr.Read())
            {
                DataRow drow = dt.NewRow();
                drow[0] = "三、原油配置量";
                drow[1] = dr["bysj"].ToString();
                drow[2] = dr["bnlj"].ToString();
                drow[3] = dr["qntqlj"].ToString();
                drow[4] = dr["zbbm"].ToString();
                dt.Rows.Add(drow);
            }
            else
            {
                DataRow drow = dt.NewRow();
                drow[0] = "三、原油配置量";
                drow[1] = "";
                drow[2] = "";
                drow[3] = "";
                drow[4] = "0";
                dt.Rows.Add(drow);
            }
            dr.Close();
            #endregion
            #region 供石化集团
            sSql = @"select round((a.bnlj-nvl(b.bnlj,0)),0) bysj,round(a.bnlj,0) bnlj,round(a.qntqlj,0) qntqlj,a.zbbm from 
                (select bnlj,qntqlj,zbbm from tg03 t where nf={1} and yf={0} and zbbm='TG030018' and dwbm='{2}') a,
                (select bnlj from tg03 t where nf={1} and yf={0}-1 and zbbm='TG030018' and dwbm='{2}') b ";
            dr = (OracleDataReader)Maticsoft.DBUtility.DbHelperOra.ExecuteReader(string.Format(sSql, yf, nf, "HB"));
            if (dr.Read())
            {
                DataRow drow = dt.NewRow();
                drow[0] = "  供石化集团";
                drow[1] = dr["bysj"].ToString();
                drow[2] = dr["bnlj"].ToString();
                drow[3] = dr["qntqlj"].ToString();
                drow[4] = dr["zbbm"].ToString();
                dt.Rows.Add(drow);
            }
            else
            {
                DataRow drow = dt.NewRow();
                drow[0] = "  供石化集团";
                drow[1] = "";
                drow[2] = "";
                drow[3] = "";
                drow[4] = "0";
                dt.Rows.Add(drow);
            }
            dr.Close();
            #endregion
            #region 地方炼油企业
            sSql = @"select round((a.bnlj-nvl(b.bnlj,0)),0) bysj,round(a.bnlj,0) bnlj,round(a.qntqlj,0) qntqlj,a.zbbm from 
                (select bnlj,qntqlj,zbbm from tg03 t where nf={1} and yf={0} and zbbm='TG030019' and dwbm='{2}') a,
                (select bnlj from tg03 t where nf={1} and yf={0}-1 and zbbm='TG030019' and dwbm='{2}') b ";
            dr = (OracleDataReader)Maticsoft.DBUtility.DbHelperOra.ExecuteReader(string.Format(sSql, yf, nf, "HB"));
            if (dr.Read())
            {
                DataRow drow = dt.NewRow();
                drow[0] = "  地方炼油企业";
                drow[1] = dr["bysj"].ToString();
                drow[2] = dr["bnlj"].ToString();
                drow[3] = dr["qntqlj"].ToString();
                drow[4] = dr["zbbm"].ToString();
                dt.Rows.Add(drow);
            }
            else
            {
                DataRow drow = dt.NewRow();
                drow[0] = "  地方炼油企业";
                drow[1] = "";
                drow[2] = "";
                drow[3] = "";
                drow[4] = "0";
                dt.Rows.Add(drow);
            }
            dr.Close();
            #endregion
            #region 地方小炼厂
            sSql = @"select round((a.bnlj-nvl(b.bnlj,0)),0) bysj,round(a.bnlj,0) bnlj,round(a.qntqlj,0) qntqlj,a.zbbm from 
                (select bnlj,qntqlj,zbbm from tg03 t where nf={1} and yf={0} and zbbm='TG030020' and dwbm='{2}') a,
                (select bnlj from tg03 t where nf={1} and yf={0}-1 and zbbm='TG030020' and dwbm='{2}') b ";
            dr = (OracleDataReader)Maticsoft.DBUtility.DbHelperOra.ExecuteReader(string.Format(sSql, yf, nf, "HB"));
            if (dr.Read())
            {
                DataRow drow = dt.NewRow();
                drow[0] = "  地方小炼厂";
                drow[1] = dr["bysj"].ToString();
                drow[2] = dr["bnlj"].ToString();
                drow[3] = dr["qntqlj"].ToString();
                drow[4] = dr["zbbm"].ToString();
                dt.Rows.Add(drow);
            }
            else
            {
                DataRow drow = dt.NewRow();
                drow[0] = "  地方小炼厂";
                drow[1] = "";
                drow[2] = "";
                drow[3] = "";
                drow[4] = "0";
                dt.Rows.Add(drow);
            }
            dr.Close();
            #endregion
            #region 损耗量
            DataRow drow3 = dt.NewRow();
            drow3[0] = "四、损耗量";
            drow3[1] = "";
            drow3[2] = "";
            drow3[3] = "";
            drow3[4] = "18";
            dt.Rows.Add(drow3);
            #endregion
            #region 原油
            sSql = @"select round((a.bnlj-nvl(b.bnlj,0)),0) bysj,round(a.bnlj,0) bnlj,round(a.qntqlj,0) qntqlj,a.zbbm from 
                (select bnlj,qntqlj,zbbm from tg03 t where nf={1} and yf={0} and zbbm='TG030023' and dwbm='{2}') a,
                (select bnlj from tg03 t where nf={1} and yf={0}-1 and zbbm='TG030023' and dwbm='{2}') b ";
            dr = (OracleDataReader)Maticsoft.DBUtility.DbHelperOra.ExecuteReader(string.Format(sSql, yf, nf, "HB"));
            if (dr.Read())
            {
                DataRow drow = dt.NewRow();
                drow[0] = "  原油";
                drow[1] = dr["bysj"].ToString();
                drow[2] = dr["bnlj"].ToString();
                drow[3] = dr["qntqlj"].ToString();
                drow[4] = dr["zbbm"].ToString();
                dt.Rows.Add(drow);
            }
            else
            {
                DataRow drow = dt.NewRow();
                drow[0] = "  原油";
                drow[1] = "";
                drow[2] = "";
                drow[3] = "";
                drow[4] = "0";
                dt.Rows.Add(drow);
            }
            dr.Close();
            #endregion
            #region 天然气
            sSql = @"select round((a.bnlj-nvl(b.bnlj,0)),0) bysj,round(a.bnlj,0) bnlj,round(a.qntqlj,0) qntqlj,a.zbbm from 
                (select bnlj,qntqlj,zbbm from tg03 t where nf={1} and yf={0} and zbbm='TG030041' and dwbm='{2}') a,
                (select bnlj from tg03 t where nf={1} and yf={0}-1 and zbbm='TG030041' and dwbm='{2}') b ";
            dr = (OracleDataReader)Maticsoft.DBUtility.DbHelperOra.ExecuteReader(string.Format(sSql, yf, nf, "HB"));
            if (dr.Read())
            {
                DataRow drow = dt.NewRow();
                drow[0] = "  天然气";
                drow[1] = dr["bysj"].ToString();
                drow[2] = dr["bnlj"].ToString();
                drow[3] = dr["qntqlj"].ToString();
                drow[4] = dr["zbbm"].ToString();
                dt.Rows.Add(drow);
            }
            else
            {
                DataRow drow = dt.NewRow();
                drow[0] = "  天然气";
                drow[1] = "";
                drow[2] = "";
                drow[3] = "";
                drow[4] = "0";
                dt.Rows.Add(drow);
            }
            dr.Close();
            #endregion

            #region 转换成XML
            string dataString = ConfigurationManager.AppSettings["SczbIndex_Column"];
            ConvertToXml.Convert(dt, dataString, 0, dt.Rows.Count);           
            #endregion
        }
    }
}